package com.example.crm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.example.crm.pojo.Customer;
import com.example.crm.utils.JDBCUtils;

public class CustomerDAO {

	public int insert(Customer c) throws SQLException {
		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "insert into cst_customer values (null,?,?,?,?,?,?,?,?,?)";// (需要修改)
		PreparedStatement stmt = conn.prepareStatement(sql);

		// (需要修改)
		stmt.setString(1, c.getName());
		stmt.setInt(2, c.getUserId());
		stmt.setInt(3, c.getCreateId());
		stmt.setString(4, c.getSource());
		stmt.setString(5, c.getIndustry());
		stmt.setString(6, c.getLevel());
		stmt.setString(7, c.getLinkman());
		stmt.setString(8, c.getPhone());
		stmt.setString(9, c.getMobile());

		// 3. 执行层语句
		int result = stmt.executeUpdate();

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return result;
	}

	public int deleteById(int id) throws SQLException {
		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "delete from cst_customer where cust_id=?";// (需要修改)
		PreparedStatement stmt = conn.prepareStatement(sql);

		// (需要修改)
		stmt.setInt(1, id);

		// 3. 执行层语句
		int result = stmt.executeUpdate();

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return result;
	}

	public int update(Customer c) throws SQLException {
		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "update cst_customer set "
				+ "cust_name=?,"
				+ "cust_source=?,"
				+ "cust_level=?,"
				+ "cust_linkman=?,"
				+ "cust_phone=?,"
				+ "cust_mobile=? where cust_id=?";// (需要修改)
		PreparedStatement stmt = conn.prepareStatement(sql);

		// (需要修改)
		stmt.setString(1, c.getName());
		stmt.setString(2, c.getSource());
		stmt.setString(3, c.getLevel());
		stmt.setString(4, c.getLinkman());
		stmt.setString(5, c.getPhone());
		stmt.setString(6, c.getMobile());
		
		stmt.setInt(7, c.getId());

		// 3. 执行层语句
		int result = stmt.executeUpdate();

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return result;
	}

	public Customer findById(int id) throws SQLException {
		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "select * from cst_customer where cust_id=?";// (需要修改)
		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setInt(1, id);

		// 3. 执行层语句
		ResultSet rs = stmt.executeQuery();
		Customer c = null;
		if (rs.next()) {
			String name = rs.getString("cust_name");
			int userId = rs.getInt("cust_user_id");
			int createId = rs.getInt("cust_create_id");
			String source = rs.getString("cust_source");
			String industry = rs.getString("cust_industry");
			String level = rs.getString("cust_level");
			String linkman = rs.getString("cust_linkman");
			String phone = rs.getString("cust_phone");
			String mobile = rs.getString("cust_mobile");

			c = new Customer(id, name, userId, createId, source, industry, level, linkman, phone, mobile);

		}

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return c;
	}

	public List<Customer> findAll() throws SQLException {
		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "select * from cst_customer";// (需要修改)
		PreparedStatement stmt = conn.prepareStatement(sql);

		// 3. 执行层语句
		ResultSet rs = stmt.executeQuery();
		List<Customer> list = new ArrayList<>();
		while (rs.next()) {
			int id = rs.getInt("cust_id");
			String name = rs.getString("cust_name");
			int userId = rs.getInt("cust_user_id");
			int createId = rs.getInt("cust_create_id");
			String source = rs.getString("cust_source");
			String industry = rs.getString("cust_industry");
			String level = rs.getString("cust_level");
			String linkman = rs.getString("cust_linkman");
			String phone = rs.getString("cust_phone");
			String mobile = rs.getString("cust_mobile");

			Customer c = new Customer(id, name, userId, createId, source, industry, level, linkman, phone, mobile);
			list.add(c);
		}

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return list;
	}
}
